/*
   PROCEDURES LIST :	
	ADD_DOMAIN (p_Nom TEXT, p_Description TEXT, p_Thesaurus TEXT ) RETURNS BOOLEAN 
	ADD_CONCEPT (p_Nom TEXT, p_Domain TEXT, p_TermVedette TEXT, p_TermNonVedette TEXT[], p_TS TEXT[], p_TG TEXT[] ) RETURNS BOOLEAN
	ADD_TS (p_Nom TEXT, p_Concept TEXT) RETURNS BOOLEAN
	ADD_TG (p_Nom TEXT, p_Concept TEXT) RETURNS BOOLEAN 
	ADD_ASSOCIATION (p_Nom TEXT, p_Concept1 TEXT, p_Concept2 TEXT, p_Reflex BOOLEAN) RETURNS BOOLEAN
	ADD_TERMNVDT (p_Nom TEXT, p_Concept TEXT) RETURNS BOOLEAN
*/

/*
*	Function : ADD_DOMAIN 
*	Description : Ajoute un domain, renvoi FAUX si thesaurus n'existe pas, VRAI sinon
*/ 
CREATE OR REPLACE FUNCTION ADD_DOMAIN (p_Nom TEXT, p_Description TEXT, p_Thesaurus TEXT ) RETURNS BOOLEAN AS $$
DECLARE
	domain_oid OID;
	thesaurus_oid OID;
BEGIN
/* 0- verification des entrées */
	thesaurus_oid = (SELECT OID FROM Thesaurus WHERE Nom = p_Thesaurus);
	IF (thesaurus_oid IS NULL) THEN 
		RETURN FALSE;
	END IF;

/* 1- Insertion du Domain */
	INSERT INTO Domains VALUES (p_Nom, p_Description, (SELECT LOCALTIMESTAMP), ARRAY[]::OID[], thesaurus_oid);

/* 2- Update de la liste des Domains pour le Thesaurus */
	domain_oid = (SELECT OID FROM Domains WHERE Nom = p_nom);
	
	UPDATE Thesaurus SET Domains = Domains || ARRAY[domain_oid]
		WHERE OID = thesaurus_oid;
			
	RETURN TRUE;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : ADD_CONCEPT 
*	Description : Ajoute un concept, renvoi FAUX si domain n'existe pas, VRAI sinon
*/

CREATE OR REPLACE FUNCTION ADD_CONCEPT (p_Nom TEXT, p_Domain TEXT, p_TermVedette TEXT, p_TermNonVedette TEXT[], p_TS TEXT[], p_TG TEXT[] ) RETURNS BOOLEAN AS $$
DECLARE
	t TEXT; 
	TermVedette_oid OID;
	domain_oid OID;
	concept_oid OID;
BEGIN
/* 0- verification des entrées */
	domain_oid = (SELECT OID FROM Domains WHERE Nom = p_Domain);
	IF (domain_oid IS NULL) THEN 
		RETURN FALSE;
	END IF;
	
/* 1- Insertion du term vedette */
	INSERT INTO TermVdts VALUES (p_TermVedette, ARRAY[]::OID[], NULL);

/* 2- Insertion des terms Non vedette */
	TermVedette_oid = (SELECT OID FROM TermVdts WHERE Nom = p_TermVedette);
	
	FOREACH t IN  ARRAY p_TermNonVedette LOOP
		INSERT INTO TermNonVdts VALUES (t, TermVedette_oid);
	END LOOP;
	
/* 3- Update de la liste des terms Non vedette pour le term vedette */
	FOREACH t IN  ARRAY p_TermNonVedette LOOP
		UPDATE TermVdts SET EP = EP || ARRAY[(SELECT OID FROM TermNonVdts WHERE Nom = t)]
			WHERE Nom = p_TermVedette;
	END LOOP;

/* 4- Insertion du concept */
	INSERT INTO Concepts VALUES (p_Nom, domain_oid, (SELECT LOCALTIMESTAMP), TermVedette_oid, ARRAY[]::OID[], ARRAY[]::OID[], ARRAY[]::OID[]);

/* 4.1- Récupération de l'OID du concept inséré */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Nom);
	
/* 5- Update des concepts spécifique pour le concept */
	FOREACH t IN  ARRAY p_TS LOOP
		UPDATE Concepts SET TS = TS || ARRAY[(SELECT OID FROM Concepts WHERE Nom = t)] WHERE Nom = p_Nom;
	END LOOP;

/* 6-Update des concepts genérique pour le concept */
	FOREACH t IN  ARRAY p_TG LOOP
		UPDATE Concepts SET TG = TG || ARRAY[(SELECT OID FROM Concepts WHERE Nom = t)] WHERE Nom = p_Nom;
	END LOOP;

/* 7- Update des concepts genériques Reflex */
	FOREACH t IN  ARRAY p_TS LOOP
		UPDATE Concepts SET TG = TG || ARRAY[concept_oid] WHERE Nom = t;
	END LOOP;

/* 8- Update des concepts spécifiques Reflex */
	FOREACH t IN  ARRAY p_TG LOOP
		UPDATE Concepts SET TS = TS || ARRAY[concept_oid] WHERE Nom = t;
	END LOOP;

/* 9- Update du concept du terms vedette */
	UPDATE TermVdts SET concept = concept_oid WHERE Nom = p_TermVedette;

/* 10- Update de la liste des concepts pour le Domain */
	UPDATE Domains SET Concepts = Concepts || ARRAY[concept_oid] WHERE Nom = p_Domain;
	
	RETURN TRUE;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : ADD_TS 
*	Description : Ajoute concept spécifique, renvoi FAUX si concept n'existe pas, VRAI sinon
*/
CREATE OR REPLACE FUNCTION ADD_TS (p_Nom TEXT, p_Concept TEXT) RETURNS BOOLEAN AS $$
DECLARE
	concept_oid OID;
	concept_sp_oid OID;
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Nom);
	concept_sp_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept);
	IF (concept_oid IS NULL OR concept_sp_oid IS NULL ) THEN 
		RETURN FALSE;
	END IF;

/* 1- Ajout du concept spécifique */
	UPDATE Concepts SET TS = TS || ARRAY[concept_sp_oid] WHERE Nom = p_Nom;
	
/* 2- Mise à jour du concept génerique */	
	UPDATE Concepts SET TG = TG || ARRAY[concept_oid] WHERE Nom = p_Concept;
		
	RETURN TRUE;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : ADD_TG
*	Description : Ajoute concept génerique, renvoi FAUX si concept n'existe pas, VRAI sinon
*/
CREATE OR REPLACE FUNCTION ADD_TG (p_Nom TEXT, p_Concept TEXT) RETURNS BOOLEAN AS $$
DECLARE
	concept_oid OID;
	concept_gn_oid OID;
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Nom);
	concept_gn_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept);
	IF (concept_oid IS NULL OR concept_gn_oid IS NULL ) THEN 
		RETURN FALSE;
	END IF;

/* 1- Ajout du concept génerique */
	UPDATE Concepts SET TG = TG || ARRAY[concept_gn_oid] WHERE Nom = p_Nom;
	
/* 2- Mise à jour du concept spécifique */	
	UPDATE Concepts SET TS = TS || ARRAY[concept_oid] WHERE Nom = p_Concept;
	
	RETURN TRUE;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : ADD_TERMNVDT
*	Description : Ajoute term non vedette, renvoi FAUX si concept n'existe pas, VRAI sinon
*/
CREATE OR REPLACE FUNCTION ADD_TERMNVDT (p_Nom TEXT, p_Concept TEXT) RETURNS BOOLEAN AS $$
DECLARE
	TermVedette_oid OID;
	concept_oid OID;
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept);
	IF (concept_oid IS NULL) THEN 
		RETURN FALSE;
	END IF;
	
/* 1- Insertion du term Non vedette */
	TermVedette_oid = (SELECT OID FROM TermVdts WHERE concept = concept_oid);
	INSERT INTO TermNonVdts VALUES (p_Nom, TermVedette_oid);
	
/* 2- Update de la liste des terms Non vedette pour le term vedette */
	UPDATE TermVdts SET EP = EP || ARRAY[(SELECT OID FROM TermNonVdts WHERE Nom = p_Nom) ] WHERE OID = TermVedette_oid;
	
	RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

/*
*	Function : ADD_ASSOCIATION
*	Description : Ajoute association, renvoi FAUX si l'un des concepts n'existe pas, VRAI sinon
*/
CREATE OR REPLACE FUNCTION ADD_ASSOCIATION (p_Nom TEXT, p_Concept1 TEXT, p_Concept2 TEXT, p_Reflex BOOLEAN) RETURNS BOOLEAN AS $$
DECLARE
	concept1_oid OID;
	concept2_oid OID;
BEGIN
/* 0- verification des entrées */
	concept1_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept1);
	concept2_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept2);
	IF (concept1_oid IS NULL OR concept2_oid IS NULL ) THEN 
		RETURN FALSE;
	END IF;
	
/* 1- Insertion des associations */
	INSERT INTO Associations VALUES (p_Nom, concept1_oid, concept2_oid, p_Reflex);

/* 2- Update des associations pour le concept */
	UPDATE Concepts SET Associer = Associer || ARRAY[(SELECT OID FROM Associations WHERE Nom = p_Nom AND concept1 = concept1_oid	
		AND concept2 = concept2_oid)] WHERE Nom = p_Concept1;

/* 3- Si assocaition reflexive -> insertion inverse */
IF p_Reflex = TRUE THEN 
	INSERT INTO Associations VALUES (p_Nom, concept2_oid, concept1_oid, p_Reflex);

	UPDATE Concepts SET Associer = Associer || ARRAY[(SELECT OID FROM Associations WHERE Nom = p_Nom AND concept1 = concept2_oid
		AND concept2 = concept1_oid)] WHERE Nom = p_Concept2;
END IF;

	RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
